﻿using famails.common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace famails.App_Code.source.dao
{
    public class CM_CustomerCareAutoDAO
    {
        internal System.Data.DataTable GetByID(int ID, int userid)
        {
            SqlCommand cmd = new SqlCommand("SP_CM_CustomerCareAuto_Content_Get4Edit", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@CustomerCareAutoID", SqlDbType.Decimal).Value = ID;
            cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = userid;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        internal void Edit(global::famails.dto.CM_CustomerCareAutoDTO dt)
        {
            string sql = "UPDATE tblCM_CustomerCareAuto SET " +
                       "Subject = @Subject, " +
                       "ConfigId = @ConfigId, LastModifyDate=getdate(), isstop=@isstop " +
                       "WHERE CustomerCareAutoID = @CustomerCareAutoID";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@CustomerCareAutoID", SqlDbType.Int).Value = dt.CustomerCareAutoID;
            cmd.Parameters.Add("@ConfigId", SqlDbType.Int).Value = dt.ConfigId;
            cmd.Parameters.Add("@Subject", SqlDbType.NVarChar).Value = dt.Subject;
            cmd.Parameters.Add("@isstop", SqlDbType.Bit).Value = dt.IsStop;
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            cmd.ExecuteNonQuery();

            cmd.Dispose();
        }

        internal int Add(global::famails.dto.CM_CustomerCareAutoDTO dt)
        {
            string sql = "INSERT INTO tblCM_CustomerCareAuto(Subject, ConfigId, StartDate, EndDate, UserId, CustomerGroupId, ProductGroupId, CreateDate, isstop) " +
                         "VALUES(@Subject, @ConfigId, @StartDate, @EndDate, @UserId, @CustomerGroupId, @ProductGroupId, getdate(), @isstop) SELECT SCOPE_IDENTITY()";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@Subject", SqlDbType.NVarChar).Value = dt.Subject;
            cmd.Parameters.Add("@ConfigId", SqlDbType.Int).Value = dt.ConfigId;

            if (dt.StartDate != null)
                cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = dt.StartDate;
            else cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = DBNull.Value;
            if (dt.EndDate != null)
                cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = dt.EndDate;
            else cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = DBNull.Value;

            cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = dt.UserId;
            cmd.Parameters.Add("@CustomerGroupId", SqlDbType.Int).Value = dt.CustomerGroupId;
            cmd.Parameters.Add("@ProductGroupId", SqlDbType.Int).Value = dt.ProductGroupId;
            cmd.Parameters.Add("@isstop", SqlDbType.Bit).Value = dt.IsStop;
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            var eventId = cmd.ExecuteScalar();

            cmd.Dispose();
            return int.Parse(eventId.ToString());
        }

        internal void Delete(int CustomerCareAutoId)
        {
            throw new NotImplementedException();
        }

        internal DataTable Search(string subject, int c_group, int p_group, string from, string to, int userid)
        {
            SqlCommand cmd = new SqlCommand("SP_CM_CustomerCareAuto_Search", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@subject", SqlDbType.NVarChar).Value = subject;
            cmd.Parameters.Add("@from", SqlDbType.NVarChar).Value = from;
            cmd.Parameters.Add("@To", SqlDbType.NVarChar).Value = to;
            cmd.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
            cmd.Parameters.Add("@c_group", SqlDbType.Int).Value = c_group;
            cmd.Parameters.Add("@p_group", SqlDbType.Int).Value = p_group;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        internal DataTable GetReportById(int Id, int UserID)
        {
            SqlCommand cmd = new SqlCommand("[SP_CM_CustomerCareAuto_ReportById]", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@userid", SqlDbType.Int).Value = UserID;
            cmd.Parameters.Add("@autoid", SqlDbType.Int).Value = Id;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        internal void addSendMailFromCustomerCareAuto(int CustomerCareAutoID, int UserID)
        {
            string sql = "Select * From tblCM_CustomerCareAuto_Content where CustomerCareAutoId=@CustomerCareAutoId order by orderno, hoursend, id";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@CustomerCareAutoId", SqlDbType.Int).Value = CustomerCareAutoID;
            DataTable T = new DataTable();
            SqlDataAdapter data = new SqlDataAdapter(cmd);
            data.Fill(T);
            if (T != null && T.Rows.Count > 0)
            {
                int hoursendCount = 0, temp = 0;
                DateTime now = DateTime.Now;
                foreach (DataRow row in T.Rows)
                {
                    Int32.TryParse(row["hoursend"] + "", out temp);
                    hoursendCount += temp;
                    sql = "SP_AddSendMailFromCustomerCareAutoContentId";
                    cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@CustomerCareAutoId", SqlDbType.Int).Value = CustomerCareAutoID;
                    cmd.Parameters.Add("@userId", SqlDbType.Int).Value = UserID;
                    cmd.Parameters.Add("@CustomerCareAuto_ContentId", SqlDbType.Int).Value = row["id"];
                    cmd.Parameters.Add("@hourSendCount", SqlDbType.Int).Value = hoursendCount;
                    DataTable table = new DataTable();
                    if (ConnectionData._MyConnection.State == ConnectionState.Closed)
                    {
                        ConnectionData._MyConnection.Open();
                    }
                    object hoursend = cmd.ExecuteScalar();
                    temp = 0;
                    int.TryParse(hoursend + "", out temp);
                    hoursendCount = temp;
                }
            }
        }

        internal void addSendMailFromCustomerCareAutoByCustomer(decimal customerid, int userid)
        {
            string sql = "SP_CM_GetCustomerCareAutoByCustomerID";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@customerid", SqlDbType.Decimal).Value = customerid;
            DataTable T = new DataTable();
            SqlDataAdapter data = new SqlDataAdapter(cmd);
            data.Fill(T);
            if (T != null && T.Rows.Count > 0)
            {
                int id = 0;
                foreach(DataRow row in T.Rows )
                {
                    id = 0;
                    int.TryParse(row[0]+"", out id);
                    addSendMailFromCustomerCareAuto(id, customerid, userid);
                    sql = "insert into tblCM_CustomerCareAuto_Customer(CustomerCareAutoId, CustomerID, StartDate) values(@CustomerCareAutoId, @CustomerID, getdate())";
                    cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                    cmd.Parameters.Add("@customerid", SqlDbType.Decimal).Value = customerid;
                    cmd.Parameters.Add("@CustomerCareAutoId", SqlDbType.Decimal).Value = row[0];
                    cmd.ExecuteNonQuery();
                }
            }
        }
        internal void addSendMailFromCustomerCareAuto(int CustomerCareAutoID, decimal CustomerID, int UserID)
        {
            string sql = "Select * From tblCM_CustomerCareAuto_Content where CustomerCareAutoId=@CustomerCareAutoId order by orderno, hoursend, id";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@CustomerCareAutoId", SqlDbType.Int).Value = CustomerCareAutoID;
            DataTable T = new DataTable();
            SqlDataAdapter data = new SqlDataAdapter(cmd);
            data.Fill(T);
            if (T != null && T.Rows.Count > 0)
            {
                int hoursendCount = 0, temp = 0;
                DateTime now = DateTime.Now;
                foreach (DataRow row in T.Rows)
                {
                    Int32.TryParse(row["hoursend"] + "", out temp);
                    hoursendCount += temp;
                    sql = "SP_AddSendMailFromCustomerCareAutoContentByCustomerId";
                    cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@CustomerCareAutoId", SqlDbType.Int).Value = CustomerCareAutoID;
                    cmd.Parameters.Add("@userId", SqlDbType.Int).Value = UserID;
                    cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = CustomerID;
                    cmd.Parameters.Add("@CustomerCareAuto_ContentId", SqlDbType.Int).Value = row["id"];
                    cmd.Parameters.Add("@hourSendCount", SqlDbType.Int).Value = hoursendCount;
                    DataTable table = new DataTable();
                    if (ConnectionData._MyConnection.State == ConnectionState.Closed)
                    {
                        ConnectionData._MyConnection.Open();
                    }
                    object hoursend = cmd.ExecuteScalar();
                    temp = 0;
                    int.TryParse(hoursend + "", out temp);
                    hoursendCount = temp;
                }
            }
        }

        internal DataTable GetOpenMailByContentID(object contentid)
        {
            SqlCommand cmd = new SqlCommand(@"select distinct * from (select [to]openemail, OpenDate, c.*
                                            From tblsendmail e 
                                            inner join tblCM_Customers c on c.customerid = e.customerid
                                            where CustomerCareAuto_ContentID= " + contentid + " and isOpen=1) a  order by opendate desc", ConnectionData._MyConnection);
            
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
    }
}